import {
	dataType
} from './constant'
class SqlBuilder {

	constructor() {
		this.dataType = dataType;
	}

	insertRowCovert(tableName, columns, rows) {
		let temp = [];
		temp.push(`insert INTO ${tableName} (`);
		for (const c of columns) {
			temp.push(`\`${c.field}\``)
			temp.push(',')
		}
		temp = temp.slice(0, -1)
		temp.push(`) VALUES  `);
		let value = "";
		for (const row of rows) {
			temp.push(' (')
			for (const c of columns) {
				value = this.covertValue(row[c.field], c)
				if (value === '') {
					value = 'null'
				}
				temp.push(value)
				temp.push(',')
			}
			temp = temp.slice(0, -1)
			temp.push(' )')
			temp.push(' ,')
		}
		temp = temp.slice(0, -1)
		temp.push(`;`);
		return temp.join("");

	}
	updateRowCovert(tableName, columns, rows) {
		const arrs = [];
		let temp = [];
		let value = "";
		const headContent = `UPDATE ${tableName} `
		for (const row of rows) {
			temp.push(headContent);
			temp.push(' SET ')
			const primaryKeyValues = row.primaryKeyValues;
			for (const c of columns) {
				value = this.covertValue(row[c.field], c)
				if (value === '') {
					value = 'null'
				}
				temp.push(` ${c.field}=${value}`)
				temp.push(',')
			}
			temp = temp.slice(0, -1)
			temp.push(" WHERE ")
			columns.filter(c => c.primaryKey).forEach(element => {
				const value = primaryKeyValues[element.field];
				if (value) {
					temp.push(`${element.field}=${this.covertValue(value,element)}`)
				} else {
					temp.push(`${element.field} is NULL`)
				}
				temp.push(' AND ');
			});
			temp = temp.slice(0, -1)
			temp.push(`;`);
			arrs.push(temp.join(""));
		}
		return arrs;

	}
	deleteRowCovert(tableName, columns, rows) {
		const arrs = [];
		let temp = [];
		const headContent = `DELETE FROM  ${tableName} `
		for (const row of rows) {
			temp.push(headContent);
			const primaryKeyValues = row.primaryKeyValues;
			temp.push(" WHERE ")
			columns.filter(c => c.primaryKey).forEach(element => {
				const value = primaryKeyValues[element.field];
				if (value) {
					temp.push(`${element.field}=${this.covertValue(value,element)}`)
				} else {
					temp.push(`${element.field} is NULL`)
				}
				temp.push(' AND ');
			});
			temp = temp.slice(0, -1)
			temp.push(`;`);
			arrs.push(temp.join(""));
		}
		return arrs;
	}


	createDatabaseCovert(catalogName, charset, collation) {
		if (!catalogName) {
			return "";
		}
		const arr = [];
		//CREATE DATABASE  `wordpress` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
		arr.push('CREATE DATABASE ');
		arr.push('`' + catalogName + '` ');
		if (charset) {
			arr.push(` DEFAULT CHARACTER SET ${charset} `);
		}
		if (collation) {
			arr.push(` COLLATE ${collation} `);
		}
		return arr.join("");

	}

	alterDatabaseCovert(catalogName, charset, collation) {
		if (!catalogName) {
			return "";
		}
		const arr = [];
		//CREATE DATABASE  `wordpress` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
		arr.push('ALTER DATABASE ');
		arr.push('`' + catalogName + '` ');
		if (charset) {
			arr.push(` DEFAULT CHARACTER SET ${charset} `);
		}
		if (collation) {
			arr.push(` COLLATE ${collation} `);
		}
		return arr.join("");
	}

	/**
	 * 删除数据库
	 */
	deleteDatabaseCovert(catalogName) {
		if (!catalogName) {
			return "";
		}
		return `DROP DATABASE \`${catalogName}\` `
	}

	createTable(table) {
		let arr = [];
	
		arr.push(" CREATE TABLE ");
		arr.push(` \`${table.catalogName}\`.\`${table.name}\` `)
		arr.push("(")
		const primaryKeys = [];
		const singleSql = [];
		if (table.fields && table.fields.length > 0) {
			let temp = [];
			for (const i in table.fields) {
				const field = table.fields[i];
				if (field.name) {
					temp.push(` \`${field.name}\` `)
					temp.push(this.covertFieldType(field))

					if (field.primaryKey) {
						primaryKeys.push(field);
					}

					temp.push(",")
					if (i == table.fields.length - 1 && primaryKeys.length == 0) {
						temp = temp.slice(0, -1)
					}

					arr.push(temp.join(''));
					temp = []
				}
			}
			if (primaryKeys.length > 0) {
				temp.push(' PRIMARY KEY (')
				temp.push(primaryKeys.map(item => `\`${item.name}\``).join(','))
				temp.push(' )')
				arr.push(temp.join(''));
				temp = []
			}
			if (table.indexs&&table.indexs.length > 0) {
				let str = arr[arr.length - 1];
				if (!str.trim().endsWith(",")) {
					arr.push(",")
				}
				arr.push(table.indexs.map(item => this.covertIndexSql(item)).join(","))
			}
			if (table.foreignKeys&&table.foreignKeys.length > 0) {
				let str = arr[arr.length - 1];
				if (!str.trim().endsWith(",")) {
					arr.push(",")
				}
				arr.push(table.foreignKeys.filter(item=>item.name).map(item => this.covertForeignKeySql(item)).join(","))
			}

			if(table.triggers&&table.triggers.length>0){
				table.triggers.forEach(item=>singleSql.push(this.covertTrigerSql(table.name,item)))
			}

		}
		arr.push(");")
		return [...[arr.join("")],...singleSql].join("");
	}

	covertIndexSql(index) {
		return `${index.indexType&&index.indexType!='NORMAL'?index.indexType:''} 
		INDEX  ${index.name?`\`${index.name}\``:''}${index.indexClo?`(${index.indexClo})`:''}
		 ${index.indexMethod?`USING ${index.indexMethod}`:''} ${index.comment?`COMMENT '${index.comment}'`:''} `
	}

	covertForeignKeySql(foreignKey){
        return ` CONSTRAINT \`${foreignKey.name}\` FOREIGN KEY
		 (${foreignKey.field.map(item=>`\`${item}\``).join(",")}) REFERENCES \`${foreignKey.refCatalog}\`.\`${foreignKey.refTable}\` (${foreignKey.refField.map(item=>`\`${item}\``).join(",")})
		  ${foreignKey.byDelete?`ON DELETE ${foreignKey.byDelete}`:''} ${foreignKey.byUpdate?`ON UPDATE ${foreignKey.byUpdate}`:''}`
	}

	covertTrigerSql(tableName,trigger){
		return `CREATE ${trigger.DEFINER?`DEFINER= ${trigger.DEFINER}`:''}  TRIGGER \`${trigger.name}\`  ${trigger.type} ${trigger.triggerTime} ON \`${tableName}\` FOR EACH ROW ${trigger.ACTION_STATEMENT?trigger.ACTION_STATEMENT:''};`
	}


	alterTable(fullFields, table) {
		if(table.fields.updateRecords.length===0&&
			table.fields.insertRecords.length===0&&
			table.fields.removeRecords.length===0&&
			table.indexs.insertRecords.length===0&&
			table.indexs.updateRecords.length===0&&
			table.indexs.removeRecords.length===0&&
			table.foreignKeys.insertRecords.length===0&&
			table.foreignKeys.updateRecords.length===0&&
			table.foreignKeys.removeRecords.length===0&&
			table.triggers.insertRecords.length===0&&
			table.triggers.updateRecords.length===0&&
			table.triggers.removeRecords.length===0){
				return ;
			}
		let arr = [];
		const singleSql = [];
		
		arr.push("ALTER TABLE ");
		arr.push(`\`${table.catalogName}\`.\`${table.name}\``)
		let startSQL = arr.join("")+";";
		let primaryKeys = [];
		let temp = [];
		//处理更新字段信息
		if (table.fields && table.fields.updateRecords && table.fields.updateRecords.length > 0) {


			for (const i in table.fields.updateRecords.filter(item=>table.fields.insertRecords,length===0||table.fields.insertRecords.some(inserItem=>inserItem.name!=item.name))) {
				const field = table.fields.updateRecords[i];
				if (field.name) {
					if (field.name === field.key) {
						temp.push(' MODIFY COLUMN ')
						temp.push(` \`${field.name}\` `)
					} else {
						temp.push(' CHANGE COLUMN ')
						temp.push(` \`${field.key}\` `)
						temp.push(` \`${field.name}\` `)
					}
					temp.push(" " + this.covertFieldType(field))
					if (field.primaryKey&&field.primaryKey!=field.oldPrimaryKey) {
						primaryKeys.push(field);
					}
					temp.push(",")
					arr.push(temp.join(''));
					temp = []
				}
			}
		}
		
		//处理删除字段信息
		if (table.fields && table.fields.removeRecords && table.fields.removeRecords.length > 0) {
			let temp = [];
			for (const i in table.fields.removeRecords) {
				const field = table.fields.removeRecords[i];
				if (field.name) {

					temp.push(' DROP COLUMN ')
					temp.push(` \`${field.name}\` `)
					temp.push(",")
					if (field.primaryKey) {
						primaryKeys.push(field);
					}
					arr.push(temp.join(''));
					temp = []
				}
			}
		}
		//处理添加字段信息
		if (table.fields && table.fields.insertRecords && table.fields.insertRecords.length > 0) {
			let temp = [];
			for (const i in table.fields.insertRecords) {
				const field = table.fields.insertRecords[i];
				if (field.name) {

					temp.push(' ADD COLUMN ')

					temp.push(` \`${field.name}\` `)

					temp.push(" " + this.covertFieldType(field))
					if (field.primaryKey) {
						primaryKeys.push(field);
					}
					temp.push(",")
					arr.push(temp.join(''));
					temp = []
				}
			}
		}


		if (primaryKeys.length > 0) {
			 if(fullFields.some(item=>item.oldPrimaryKey)){
				arr.push("DROP PRIMARY KEY")
				arr.push(",")
			 }
			//处理删除字段信息
			if (fullFields && fullFields.length > 0) {

				primaryKeys = [];
				for (const i in fullFields) {
					const field = fullFields[i];
					if (field.primaryKey) {
						primaryKeys.push(field);
					}
				}

				if (primaryKeys.length > 0) {
					temp = []
					temp.push(' ADD  PRIMARY KEY (')
					temp.push(primaryKeys.map(item => `\`${item.name}\``).join(','))
					temp.push(' )')
					temp.push(' USING BTREE')
					temp.push(",")
					arr.push(temp.join(''));
					temp = []
				}

			}


		}
        //处理索引部分
		const dropIndex = [];
		if(table.indexs.updateRecords.length > 0){
           for(const index of table.indexs.updateRecords){
			  if(index.oldIndexClo!=index.indexClo||
				index.oldIndexType!=index.indexType||
				index.oldIndexMethod!=index.indexMethod||
				index.oldComment!=index.comment){
					dropIndex.push(index)
				}else{
					//RENAME INDEX `455` TO `4556`,
					arr.push(` RENAME INDEX \`${index.key}\` TO \`${index.name}\`,`)
				}
		   }
		}
		[...dropIndex,...table.indexs.removeRecords]
		.forEach(item=>{
			arr.push(` DROP INDEX \`${item.name}\``);
			arr.push(",")
		});

		//处理外键部分
		if(table.foreignKeys.updateRecords.length > 0){
			for(const item of table.foreignKeys.updateRecords){
			    singleSql.push(` ALTER TABLE  \`${table.catalogName}\`.\`${table.name}\` DROP FOREIGN KEY \`${item.key}\`;`)
				arr.push(' ADD '+this.covertForeignKeySql(item))
				arr.push(",")
			}
		 }
		 if(table.foreignKeys.removeRecords.length > 0){
			for(const item of table.foreignKeys.removeRecords){
			    singleSql.push(` ALTER TABLE  \`${table.catalogName}\`.\`${table.name}\` DROP FOREIGN KEY \`${item.key}\`;`)
			}
		 }
		 if(table.foreignKeys.insertRecords.length > 0){
			for(const item of table.foreignKeys.insertRecords){
				arr.push(' ADD '+this.covertForeignKeySql(item))
				arr.push(",")
			}
		 }


		 //处理触发器部分
		 if(table.triggers.insertRecords.length>0){
			table.triggers.insertRecords.forEach(item=>singleSql.push(this.covertTrigerSql(table.name,item)))
		 }
		 if(table.triggers.updateRecords.length>0){
			table.triggers.updateRecords.forEach(item=>{
				singleSql.push(`DROP TRIGGER \`${item.name}\`;`)
				singleSql.push(this.covertTrigerSql(table.name,item))}
				)
		 }
		 if(table.triggers.removeRecords.length>0){
			table.triggers.removeRecords.forEach(item=>{
				singleSql.push(`DROP TRIGGER \`${item.name}\`;`)}
				)
		 }



		let  str = [...dropIndex,...table.indexs.insertRecords]
		.map(item=>{
			return  ` ADD ${this.covertIndexSql(item)}`;
		}).join(",")
		if(str){
			arr.push(str)
		}


		debugger
		
	
		
		str = arr[arr.length - 1];
		str = str.replace(/,$/, ';').trim();
		if (!str.endsWith(';')) {
			str = str + ";"
		}
		arr[arr.length - 1] = str;
		let sql = arr.join("").trim();

		if(sql===startSQL.trim()){
		    return singleSql.join("")
		}
		return [...singleSql,...[sql]].join("");

	}


	selectColumns(catalogName, tableName) {
		return `SELECT a.COLUMN_NAME name,a.COLUMN_DEFAULT column_default,a.IS_NULLABLE nullable, a.DATA_TYPE type,
		a.CHARACTER_MAXIMUM_LENGTH,a.NUMERIC_PRECISION,a.NUMERIC_SCALE,a.DATETIME_PRECISION,a.CHARACTER_SET_NAME,
		a.COLLATION_NAME,a.COLUMN_TYPE,a.COLUMN_KEY,a.EXTRA,a.COLUMN_COMMENT
		 FROM information_schema.\`COLUMNS\` a WHERE  a.TABLE_SCHEMA='${catalogName}' AND a.TABLE_NAME='${tableName}'  ORDER BY a.ORDINAL_POSITION `
	}

	selectIndexs(catalogName, tableName) {
		return `SELECT * FROM information_schema.STATISTICS a WHERE  a.TABLE_SCHEMA='${catalogName}' AND a.TABLE_NAME='${tableName}' ORDER BY a.INDEX_NAME`
	}

	selectCatalogs(){
       return  'select schema_name,default_character_set_name,default_collation_name from information_schema.schemata'
	}

	selectFunctions(catalog){
		return `SELECT ROUTINE_NAME FROM  information_schema.ROUTINES WHERE ROUTINE_SCHEMA= '${catalog}' order by ROUTINE_NAME`
	}

	selectTables(catalog){
		return `select  t1.* , t2.CHARACTER_SET_NAME  from information_schema.tables t1 
		left join   information_schema.COLLATION_CHARACTER_SET_APPLICABILITY  t2  on t1.table_collation = t2.collation_name 
		where   t1.table_schema='${catalog}'  AND t1.TABLE_TYPE IN ('BASE TABLE','SYSTEM VIEW')`
	}

	selectTableData(catalog,tableName){
		return `select * from \`${catalog}\`.\`${tableName}\``
	}

	selectViews(catalog){
		return  `select  t1.* , t2.CHARACTER_SET_NAME  from information_schema.tables t1 
		left join   information_schema.COLLATION_CHARACTER_SET_APPLICABILITY  t2  on t1.table_collation = t2.collation_name 
		where   t1.table_schema='${catalog}'  AND t1.TABLE_TYPE IN ('VIEW')`
	}

	selectTableForeignKeys(catalog,tableName){
		return `SELECT y.*,u.REFERENCED_TABLE_NAME,u.REFERENCED_TABLE_SCHEMA,u.COLUMN_NAME,u.REFERENCED_COLUMN_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS y,information_schema.KEY_COLUMN_USAGE u 
		 WHERE u.constraint_name=y.constraint_name AND y.constraint_schema = '${catalog}' AND y.table_name='${tableName}';`
	}

	selectTableTriggers(catalog,tableName){
		return `SELECT a.TRIGGER_NAME,a.TRIGGER_SCHEMA,a.EVENT_OBJECT_TABLE,
		a.EVENT_MANIPULATION,a.ACTION_TIMING,a.\`DEFINER\`,a.ACTION_STATEMENT FROM information_schema.TRIGGERS a 
		WHERE a.TRIGGER_SCHEMA='${catalog}' AND a.EVENT_OBJECT_TABLE='${tableName}'`
	}

	





	covertFieldType(field) {

		if (!field.type) {
			return '';
		}
		let dataType = this.dataType.find(item => item.name == field.type);
		if (!dataType) {
			console.error('类型选择错误：' + field.type)
			return '';
		}
		return dataType.buildSql(field)

	}

	covertValue(value, column) {
		if (value == null || value == undefined) {
			return ''
		}
		if (column.type === 'NUMBER') {
			return value;
		}
		return `'${value}'`


	}

}

export {
	SqlBuilder
}
